﻿// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;

namespace SnapObjects.Data.Informix
{
    internal abstract class InformixRoutineParameterSchemaProvider : RoutineParameterSchemaProvider
    {
        public InformixRoutineParameterSchemaProvider(DataContext dataContext) : base(dataContext)
        {
        }

        protected abstract bool IsProcedure { get; }

        protected override bool SkipReturnParameter => true;

        protected override IList<DynamicModel> QueryParameters(
            RoutineNameInfo nameInfo, ISqlGenerationHelper helper)
        {
            var condition = this.GetCondition(nameInfo, helper);

            return _dataContext.SqlExecutor.Select<DynamicModel>(this.GetSchemaQuerySql(condition));
        }

        protected async override Task<IList<DynamicModel>> QueryParametersAsync(
            RoutineNameInfo nameInfo, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            var condition = await this.GetConditionAsync(nameInfo, helper, cancellationToken);

            return await _dataContext.SqlExecutor.SelectAsync<DynamicModel>(
                this.GetSchemaQuerySql(condition), cancellationToken);
        }

        protected override (string, TypeMapping) GetParameterType(DynamicModel parameter, byte precision, byte scale)
        {
            var typeName = parameter.GetValue<string>("parameter_datatype");

            if ("boolean".Equals(typeName, StringComparison.OrdinalIgnoreCase))
            {
                typeName = "varchar";

                var typeMapper = _dataContext.AdoDbSqlProvider.TypeMapper;

                var mapping = typeMapper.FindMapping(typeName);

                return (typeName, mapping);
            }

            return base.GetParameterType(parameter, precision, scale);
        }

        private string GetCondition(RoutineNameInfo nameInfo, ISqlGenerationHelper helper)
        {
            var procedureName = helper.RemoveDelimitIdentifier(nameInfo.Name);
            var schema = helper.RemoveDelimitIdentifier(nameInfo.Schema);

            var condition = $" lower(p.procname)=lower('{procedureName}') ";

            if (!String.IsNullOrWhiteSpace(schema))
            {
                condition += $" and p.owner='{schema}' ";
            }
            else
            {
                var checkProcedureSql = this.GetCheckProcedureQuerySql(procedureName);

                var ret = _dataContext.SqlExecutor.Scalar<string>(checkProcedureSql);

                if (String.IsNullOrWhiteSpace(ret))
                {
                    condition += " and p.owner='informix'";
                }
                else
                {
                    condition += $" and p.owner='{ret.Trim()}'";
                }
            }
            return condition;
        }

        private async Task<string> GetConditionAsync(
            RoutineNameInfo nameInfo, ISqlGenerationHelper helper, CancellationToken cancellationToken)
        {
            var routineName = helper.RemoveDelimitIdentifier(nameInfo.Name);
            var schema = helper.RemoveDelimitIdentifier(nameInfo.Schema);

            var condition = $" lower(p.procname)=lower('{routineName}') ";

            if (!String.IsNullOrWhiteSpace(schema))
            {
                condition += $" AND p.owner='{schema}' ";
            }
            else
            {
                var checkProcedureSql = this.GetCheckProcedureQuerySql(routineName);

                var ret = await _dataContext.SqlExecutor.ScalarAsync<string>(checkProcedureSql, cancellationToken);

                if (String.IsNullOrWhiteSpace(ret))
                {
                    condition += " and p.owner='informix'";
                }
                else
                {
                    condition += $" and p.owner='{ret.Trim()}'";
                }
            }

            return condition;
        }

        private string GetSchemaQuerySql(string condition)
        {
            var schemaQuerySql = @"
select p.procid as object_uniqueidentifier, 
       c.paramname as parameter_name,
       nvl(t.name, case c.paramtype
                   when 0 then trim('char')
                   when 1 then trim('smallint')
                   when 2 then trim('integer')
                   when 3 then trim('float')
                   when 4 then trim('smallfloat')
                   when 5 then trim('decimal')
                   when 6 then trim('serial')
                   when 7 then trim('date')
                   when 8 then trim('money')
                   when 9 then trim('null')
                   when 10 then trim('datetime')
                   when 11 then trim('byte')
                   when 12 then trim('text')
                   when 13 then trim('varchar')
                   when 14 then trim('interval')
                   when 15 then trim('nchar')
                   when 16 then trim('nvarchar')
                   when 17 then trim('int8')
                   when 18 then trim('serial8')
                   when 19 then trim('set')
                   when 20 then trim('multiset')
                   when 21 then trim('list')
                   when 22 then trim('row')
                   when 23 then trim('collection')
                   when 40 then trim('lvarchar')
                   when 41 then trim('blob')
                   when 43 then trim('lvarchar')
                   when 45 then trim('boolean')
                   when 52 then trim('bigint')
                   when 53 then trim('bigserial')
                   when 2061 then trim('idssecuritylabel')
                   ELSE trim('row') end) as parameter_datatype,
       case c.paramattr
            when 1 then trim('input')
            when 2 then trim('inout')
            when 3 then trim('return')
            when 4 then trim('out')
            when 5 then trim('return')
            ELSE trim('unknown') end as parameter_mode,
       paramlen as parameter_precision, 
       0 as parameter_scale,
       nvl(null, null) as parameter_defaultvalue,
       'f' as type 
  from sysprocedures p
  LEFT JOIN sysproccolumns c ON p.procid=c.procid
  LEFT JOIN sysxtdtypes t ON c.paramxid = t.extended_id AND t.mode = 'B'";

            schemaQuerySql = this.IsProcedure ?
                schemaQuerySql + " where isproc='t' and {0};" :
                schemaQuerySql + " where isproc='f' and {0};";

            return String.Format(schemaQuerySql, condition);
        }

        private string GetCheckProcedureQuerySql(string procedureName)
        {
            var sql = @" select owner from sysprocedures where procname='{0}'";

            return String.Format(sql, procedureName);
        }
    }
}
